<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
	<%@ page import="java.sql.*"%>
<%@ page import="java.io.*"%>
<%@ page import="java.util.*,Database.DBConnection"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%int counteralt = 0;
int counteralt1 = 0; %>
<jsp:include page="t_header.jsp" />

<script type="text/javascript">

</script>
<!--  <div class="breadcrumbs_container">
	<article class="breadcrumbs"> <a href="Proc_Process.jsp">Process</a>
	<div class="breadcrumb_divider"></div>
	<a class="current">Supporting Processes</a> </article>
</div> -->
<jsp:include page="header.html" />
<!-- Declare sidebar -->
<jsp:include page="t_sidebarProcess.jsp" />

<!-- Main Content -->
<body>
	<section id="main" class="column"> <article
		class="module width_full" style="border: 1px solid #fafafa;">


	
	<table class="tablesorter tableproccess" border="0px" cellspacing="0">
	
	<tr>
	<td class="blueprocessheader"  colspan="6">
	<h2 class="blueprocessh1" >Supporting Processes -
		Procedures & Forms</h2>
	</td>
	</tr>
		<tr class="proccessth">
			<td style="width:20%">Key Process</td>
			<td style="width:13%">Code</td>
			<td style="width:20%">Procedure</td>
			<td style="width:13%">Procedure<br>Effective<br>Date
			</td>
			<td style="width:20%">Form</td>
			<td style="width:13%">Form<br>Effective<br>Date
			</td>

		</tr>
		<% 
		//get distinct values from processes table.
		String rowClass="";
		try {
			 String connectionURL1st = "jdbc:mysql://localhost:3306/qmsdb";
			 Connection connection1st = null;
			 Statement statement1st = null;
			 ResultSet rs1st = null;
	  			
	  			connection1st=DBConnection.connect(true); 
			 statement1st = connection1st.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			 String QueryString1st = "SELECT DISTINCT(ProcessName) from qmsdb.processes WHERE Category='SupportingProcesses'";
			 rs1st = statement1st.executeQuery(QueryString1st);
			 List<String> tempProcessName=new ArrayList<String>();
			 while (rs1st.next())
			 {
				 tempProcessName.add(rs1st.getString("ProcessName"));
			 }			 
		
			// close all the connections.
			rs1st.close();
			statement1st.close();
			connection1st.close();
			 for(int t=0;t<tempProcessName.size();t++)
				{
				 counteralt1++;
				 //counteralt=0;
				//coding for retrieving database count
				 try {
				  String connectionURL = "jdbc:mysql://localhost:3306/qmsdb";
				  Connection connection = null;
				  Statement statement = null;
				  ResultSet rs = null;
				  Class.forName("com.mysql.jdbc.Driver").newInstance();
				  connection = DBConnection.connect(true);
				  statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
				  String QueryString = "SELECT count(processName) as rowcount FROM qmsdb.processes as proccess left outer join qmsdb.forms as form on proccess.idProc = form.idProc Where processName='"+tempProcessName.get(t)+"'";
				  rs = statement.executeQuery(QueryString);
				 int count=0;
				  if (rs.next())
				  {
				  	count = rs.getInt("rowcount") ;
				  }
				  System.out.println(count);
				 %>				  
				 	<tr>
				 		<td class="<% 
				 				 				 		
				 		if((counteralt1) %2 != 0)
				 			{
				 			%>oddproccessth<%
				 			}
				 		else{
				 			%>evenproccessth<%
				 		}
				 			
				 			%>" rowspan="<%=count+1 %>"><%=tempProcessName.get(t)%></td>	 		
				 		<%			
				 // close all the connections.
				 rs.close();
				 statement.close();
				 connection.close();
				 }
				  catch (Exception ex) {
				 }
				 finally{
				 }
				 %>
				 		<%
				 try {
				  String connectionURL = "jdbc:mysql://localhost:3306/qmsdb";
				  Connection connection = null;
				  Statement statement = null;
				  ResultSet rs = null;
		  			
		  			connection=DBConnection.connect(true); 
				  statement = connection.createStatement();
				  String QueryString = "SELECT KP.Code,KP.ProcedureName,KP.ProcedureURL,KP.ProcLastUpdate,F.FormName,F.FormURL,F.FormLastUpdate FROM qmsdb.processes AS KP LEFT OUTER JOIN qmsdb.forms AS f ON KP.idProc=F.idProc WHERE KP.ProcessName='"+tempProcessName.get(t)+"' Order by KP.Code";
				  rs = statement.executeQuery(QueryString);
				 String tempProcID="";
				 String tempProcURL="";
				 String tempProcLastUpdate="";
				 String tempCode="";
				 List<String> tempFormID=new ArrayList<String>();
				 List<String> tempFormURL=new ArrayList<String>();
				 List<String> tempFormLastUpdate=new ArrayList<String>();
				   while(rs.next()){  
				   	
				   	if(tempProcID.equals(rs.getString("KP.ProcedureName")))
				   	{
				   		//print add to the form
				   		tempFormID.add(rs.getString("F.FormName"));
				   		tempFormURL.add(rs.getString("F.FormURL"));
				   		tempFormLastUpdate.add(rs.getString("F.FormLastUpdate"));
				   	}
				   	else
				   	{
				   		//print out list of stored details
				   		if(tempFormID.size()>0)
				   		{
				   			counteralt++;
				   			if ((counteralt)%2!=0)
				   				rowClass="oddproccesstr";
				   			else
				   				rowClass="evenproccesstr";
				   		%>
				   			<tr class="<%=rowClass%>">
				 			<td rowspan="<%=tempFormID.size()%>"><%=tempCode%></td>
				 			<td rowspan="<%=tempFormID.size()%>"><a href="<%=tempProcURL%>"><%=tempProcID%></a></td>
				 			<td rowspan="<%=tempFormID.size()%>"><%=tempProcLastUpdate%></td>
				 			
				 			<%
				   			for(int i=0;i<tempFormID.size();i++)
				   			{
				   				if (tempFormID.get(i)==null) {
				   				%>
				   				<td></td><td></td></tr>
				   				<%} else {
				   				if (i==0) {
				   				%>
				   					<td><a href="<%=tempFormURL.get(i)%>"><%=tempFormID.get(i)%></a></td>
				   					<td><%=tempFormLastUpdate.get(i)%></td></tr>
				   				<%} else {%>
				   					<tr class="<%=rowClass%>"><td><a href="<%=tempFormURL.get(i)%>"><%=tempFormID.get(i)%></a></td>
				   					<td><%=tempFormLastUpdate.get(i)%></td></tr>
				   				<%}

				   				}
				   			}%>
				
				   			
				   		<%
				   			
				   			tempFormID.clear();
				   			tempFormURL.clear();
				   			tempFormLastUpdate.clear();
				   			
				   			//Create for the current record
				   			tempProcID=rs.getString("KP.ProcedureName");
				   			tempProcURL=rs.getString("KP.ProcedureURL");
				   			tempProcLastUpdate=rs.getString("KP.ProcLastUpdate");
				   			tempCode=rs.getString("KP.Code");
				   			tempFormID.add(rs.getString("F.FormName"));
				   			tempFormURL.add(rs.getString("F.FormURL"));
				   			tempFormLastUpdate.add(rs.getString("F.FormLastUpdate"));
				   			
				   		}
				   		
				   		else 
				   		{//create new record if non of the records match.
				   			tempProcID=rs.getString("KP.ProcedureName");
				   			tempProcURL=rs.getString("KP.ProcedureURL");
				   			tempProcLastUpdate=rs.getString("KP.ProcLastUpdate");
				   			tempCode=rs.getString("KP.Code");
				   			tempFormID.add(rs.getString("F.FormName"));
				   			tempFormURL.add(rs.getString("F.FormURL"));
				   			tempFormLastUpdate.add(rs.getString("F.FormLastUpdate"));
				   		}
				   		
				   		
				   	}
				   

				 }
				 //print the last set of records
							counteralt++;
				   			if ((counteralt)%2!=0)
				   				rowClass="oddproccesstr";
				   			else
				   				rowClass="evenproccesstr";
				 %>
				 			<tr class="<%=rowClass%>">
				 			<td rowspan="<%=tempFormID.size()%>"><%=tempCode%></td>
				 			<td rowspan="<%=tempFormID.size()%>"><a href="<%=tempProcURL%>"><%=tempProcID%></a></td>
				 			<td rowspan="<%=tempFormID.size()%>"><%=tempProcLastUpdate%></td>

				 			<%
				   			for(int i=0;i<tempFormID.size();i++)
				   			{
				   				if (tempFormID.get(i)==null) {
					   				%>
					   				<td></td><td></td></tr>
					   				<%} else {
					   				if (i==0) {
					   				%>
					   					<td><a href="<%=tempFormURL.get(i)%>"><%=tempFormID.get(i)%></a></td>
					   					<td><%=tempFormLastUpdate.get(i)%></td></tr>
					   				<%} else {%>
					   					<tr class="<%=rowClass%>"><td><a href="<%=tempFormURL.get(i)%>"><%=tempFormID.get(i)%></a></td>
					   					<td><%=tempFormLastUpdate.get(i)%></td></tr>
					   				<%}

					   				}
				   			}%>
				   			
				 <%
				 // close all the connections.
				 rs.close();
				 statement.close();
				 connection.close();
				 }
				  catch (Exception ex) {
				 }
				 finally{
				 }
				 %>
				 		<% 
				}
			}
			 catch (Exception ex) {
			}
			finally{
			}
			
		%>
	</table>

	</article> 
	<br /><br />
	</section>
	<div class="spacer"></div>


	<!-- Declare closing tags -->
	<jsp:include page="t_footer.jsp" />